package com.faceye.core.repository.jdbc;

import com.faceye.core.repository.jdbc.wrapper.Column;
import com.faceye.core.repository.jdbc.wrapper.Row;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.util.ReflectionUtils;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class SQLExecuteUtil {
    private  static String xdbName = "";

    private SQLExecuteUtil(String dbName){
        xdbName=dbName;
    }

    /**
     * 每次返回一个新的实例
     * @param dbName
     * @return
     */
    public synchronized static SQLExecuteUtil getInstance(String dbName){
        return new SQLExecuteUtil(dbName);
    }


    /**
     * 获取数据库连接
     * @return
     */
    public Connection getConnection(){
        DataSource dataSource = DataSourceFactory.getInstance().getDataSource(xdbName);
        Connection connection=null;
        try {
            connection= dataSource.getConnection();
        } catch (SQLException e) {
            log.error(">>get db {} connection error:{}",xdbName,e);
        }
        return connection;
    }

    /**
     * 释放数据库连接 connection 到数据库缓存池，并关闭 rSet 和 pStatement 资源
     * @param rSet 数据库处理结果集
     * @param pStatement 数据库操作语句
     * @param connection 数据库连接对象
     */
    public  void releaseSqlConnection(ResultSet rSet, PreparedStatement pStatement, Connection connection) {
        try {
            if (rSet != null) {
                rSet.close();
            }
        } catch (SQLException e) {
            log.error("ck resultset close error:{}",e);
        } finally {
            try {
                if (pStatement != null) {
                    pStatement.close();
                }
            } catch (SQLException e) {
                log.error("ck pstmt close error:{}",e);
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    log.error("ck connection close error:{}",e);
                }
            }
        }
    }

    /**
     * 获得数据库表列结构
     * @param table
     * @return
     */
    public List<Column> getColumns(String table){
        List<Column> columns=new ArrayList<Column>(0);
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs =null;
        String sql = "select * from "+table+" limit 0,1";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = pstmt.getMetaData();
            int columnCount = metaData.getColumnCount();//列数量
            for(int i=1;i<=columnCount;i++){
                String columnName = metaData.getColumnName(i);//列名,select name from table -> columnName = name
                String columnLabel = metaData.getColumnLabel(i);//标签名：select name as user_name from table -> columnName = user_name
                String columnTypeName = metaData.getColumnTypeName(i);//数据库设置列类型
                int columnType = metaData.getColumnType(i);//列数据库类型
                String columnJavaType=metaData.getColumnClassName(i);//列对应 的java类型

                //赋值
                Column column=new Column();
                column.setLabel(columnLabel);
                column.setName(columnName);
                column.setType(columnType);
                column.setTypeName(columnTypeName);
                column.setClassName(columnJavaType);
                columns.add(column);
            }

        }catch (SQLException e) {
            log.error("query sql:{},error:{}",sql,e);
        }finally {
            releaseSqlConnection(rs,pstmt,conn);
        }
        return columns;
    }

    /**
     * 查询数据
     * @param sql
     * @return
     */
    public List<Row> query(String sql){
        List<Row> res =new ArrayList<Row>(0);
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs =null;
        try {
            conn=getConnection();
            pstmt =conn.prepareStatement(sql);
            rs =pstmt.executeQuery();
            ResultSetMetaData metaData = pstmt.getMetaData();
            int columnCount  =metaData.getColumnCount();//列数量
            if(rs!=null){
                while(rs.next()) {
                      Row row =new Row();
//                    Map<String,String> row=new HashMap<String,String>();
//                    List row=new ArrayList();
//                    ResultSetMetaData data = rs.getMetaData();//数据库列名
//                    int columnCount = data.getColumnCount();//多少列
                    for (int i = 1; i <= columnCount; i++) { //获取列表 index 从1开始、列名、列类型、列的数据长度
                        Column column =new Column();
                        String columnName = metaData.getColumnName(i);//列名,select name from table -> columnName = name
                        String columnLabel = metaData.getColumnLabel(i);//标签名：select name as user_name from table -> columnName = user_name
                        String columnDBTypeName = metaData.getColumnTypeName(i);//数据库设置列类型
                        int columnType = metaData.getColumnType(i);//列数据库类型
                        String columnJavaType=metaData.getColumnClassName(i);//列对应 的java类型
                        Object value = rs.getObject(i);//数据
                        //赋值
                        column.setLabel(columnLabel);
                        column.setName(columnName);
                        column.setType(columnType);
                        column.setTypeName(columnDBTypeName);
                        column.setClassName(columnJavaType);
                        column.setValue(value);
                        row.addColumn(column);
//                        row.add(value);
                    }
                    res.add(row);
                }
            }
        } catch (SQLException e) {
            log.error("query sql:{},error:{}",sql,e);
        }finally {
            releaseSqlConnection(rs,pstmt,conn);
        }
        return  res;
    }

    /**
     *
     * @param sql
     * @param rowType
     * @param <T>
     * @Description: 对像中需要有构造方法，推荐使用：@NoArgsConstructor 注解
     * @return
     */
    public <T> List<T>  query(String sql,Class<T> rowType){
        List<T> res =new ArrayList<T>(0);
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs =null;
        try {
            conn=getConnection();
            pstmt =conn.prepareStatement(sql);
            rs =pstmt.executeQuery();
            ResultSetMetaData metaData = pstmt.getMetaData();
            int columnCount  =metaData.getColumnCount();//列数量

            if(rs!=null){
                while(rs.next()) {
                    T row = BeanUtils.instantiate(rowType);
//                    ResultSetMetaData data = rs.getMetaData();//数据库列名
//                    int columnCount = data.getColumnCount();//多少列
                    for (int i = 1; i <= columnCount; i++) { //获取列表 index 从1开始、列名、列类型、列的数据长度
                        String columnName = metaData.getColumnName(i);//列名,select name from table -> columnName = name
                        String columnLabel = metaData.getColumnLabel(i);//标签名：select name as user_name from table -> columnName = user_name
//                        data.getColumnType(i);//数据类型
                        String columnDBTypeName = metaData.getColumnTypeName(i);//数据库设置列类型
                        String columnJavaType=metaData.getColumnClassName(i);//列对应 的java类型
                        Object value = rs.getObject(i);//数据
                        String columnValue="";
                        Field field= ReflectionUtils.findField(rowType,columnLabel);
                        if(field!=null){
                            try {
                                PropertyUtils.setProperty(row,columnLabel,value);
                            } catch (IllegalAccessException e) {
                                log.error("set property {} value {} error:{}",columnLabel,value,e);
                            } catch (InvocationTargetException e) {
                                log.error("set property {} value {} error:{}",columnLabel,value,e);
                            } catch (NoSuchMethodException e) {
                                log.error("set property {} value {} error:{}",columnLabel,value,e);
                            }
                        }
                    }
                    res.add(row);
                }
            }
        } catch (SQLException e) {
            log.error("query sql:{},error:{}",sql,e);
        } finally {
            releaseSqlConnection(rs,pstmt,conn);
        }
        return  res;
    }


    public void insert(String sql,List<Row> rows){
        Connection conn=null;
        PreparedStatement pstmt=null;
        conn=getConnection();
        try {
            conn.setAutoCommit(false);
            pstmt=conn.prepareStatement(sql);
            for(Row row:rows){
                int index =1;
                for(Column column : row.getColumns()){
//                    pstmt.setObject(index,column.getValue());
//                    pstmt.setObject(index,column.getValue(),MysqlType.getByJdbcType(column.getType()));
//                    pstmt.setObject(index,column.getValue(),JDBCType.valueOf(column.getType()));
                    setColumnValue(pstmt,index,column);
                    index++;
//                    java.sql.SQLType;
                }
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            pstmt.clearBatch();
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.error(">>merlog log rollback.error:{}",e);
            }
            log.error("save merge log error:{}",e);
        }finally {
            releaseSqlConnection(null,pstmt,conn);
        }
    }





    private void setColumnValue(PreparedStatement pstmt,int index,Column column) throws SQLException {
        String typeClassName = column.getClassName();
//        log.debug(">> Type class name of column:{},{}",column.getName(),column.getClassName());
        if(StringUtils.equals(typeClassName,"java.lang.Long")){
            Long value =NumberUtils.toLong(column.getValue()==null?"0":column.getValue().toString());
            pstmt.setLong(index,value.longValue());
        }
        if(StringUtils.equals(typeClassName,"java.lang.String")){
            pstmt.setString(index,column.getValue()==null?"":column.getValue().toString());
        }
        if(StringUtils.equals(typeClassName,"java.sql.Date")){
            pstmt.setDate(index,(Date) column.getValue());
        }
    }


}
